#!/usr/bin/env python3

import os
import xlwings as xw


# 工作表类
class Sheet:
	def __init__(self, wb, sheet_name):
		self.sheetName = sheet_name
		self.ws = wb.sheets[sheet_name]

	def get_ws(self):
		return self.ws

	# 获取sheet名字
	def get_sheet_name(self):
		return self.sheetName

	# 根据单元格名获取值 例如: A1
	def get_cell_value_by_name(self, cell_name):
		return self.ws.range(cell_name).value

	# 根据行列号获取单元格值，例如： 1,1 == 第1行,第1列 == A1
	def get_cell_value(self, row, col):
		return self.ws.range((row, col)).value

	# 获取最大有效行
	def get_max_row(self):
		return self.ws.used_range.last_cell.row

	# 获取最大有效列
	def get_max_col(self):
		return self.ws.used_range.last_cell.column

	# 修改sheet名
	def update_sheet_name(self, new_sheet_name):
		self.ws.sheet_name = new_sheet_name

	# 获取第一行的值list
	def get_first_row_value(self):
		return self.ws.range((1, 1), (1, self.get_max_col())).value

	# 根据列名查找列索引，找不到返回0
	def find_col_number(self, src_col_name):
		cols = self.get_first_row_value()
		index = 0
		for value in cols:
			if value != src_col_name:
				index = index + 1
			else:
				return index + 1
		return 0

	# 根据列名找到列的range
	def get_col_list(self, col_name):
		col_number = self.find_col_number(col_name)
		if col_number == 0:
			error = '找不到列:' + col_name
			raise Exception(error)
		return self.ws.range((1, col_number), (self.get_max_row(), col_number))

	# 将指定列替换
	def replace_col(self, target_col_name, src_col_list):
		self.get_col_list(target_col_name).options(transpose=True).value = src_col_list

	# 内部列替换
	def replace_col_inner(self, target_col_name, src_col_name):
		self.get_col_list(target_col_name).options(transpose=True).value = self.get_col_list(src_col_name).value

	# 删除指定单元格内容 col = A1
	def delete_cell(self, col):
		self.ws.range(col).clear()
